<?php

class sqlserver{
	const RESULT_FIELD_NAME_LOWER = false;
	private $host;
	private $username;
	private $password;
	private $dbname;
	private $charset;
	var $conn= false;
	
	public function __construct($conninfo){
		$this->host = $conninfo['host'];
		$this->username = $conninfo['username'];
		$this->password = $conninfo['password'];
		$this->dbname = $conninfo['dbname'];
		$this->charset = $conninfo['charset']?$conninfo['charset']:'utf8';
		$this->conn = mssql_connect($this->host,$this->username,$this->password);
		mssql_select_db($this->dbname,$this->conn) or die("无法找到数据库，请确认数据库名称正确！");
		if (!$this->conn){
           JYTools::error('数据库存连接失败！');	
           return;
        }
	}
	
	function execute($sql){
		//貌似本身有一个mssql_execute用来执行存储过程的
		$stmt = &$this->_execute($sql);		
	
		$rs = $stmt? mssql_rows_affected($this->conn):-1;
		if($stmt instanceof mssql_result) mssql_free_result($stmt);
		return $rs;
	}

	function query($sql, $page = 0, $pageSize = 10,&$totalCount=0){
		//exit("query:".$page);
		//不分页
		if(!$page){
			$stmt = &$this->_execute($sql);
			$data = $this->fetchArray($stmt);
			$totalCount= count($data);
			return $data;
		}
		
		$sqlCount = "select count(1) cnt  from (".$sql.") t";
		$totalCount = $this->query($sqlCount);	
		$totalCount = $totalCount[0]['cnt'];
		
		//总页数
		$lastPage = ceil($totalCount/$pageSize);

		//exit($lastPage);
		//第一页或中间页
		if($page<$lastPage){
			$sql = "select top ".($page*$pageSize)." * from ({$sql}) t";
		}

		$stmt = &$this->_execute($sql);
		$offset = ($page-1)*$pageSize;
			
		$data = $this->fetchArray($stmt,$offset,$pageSize);
		return $data;
	}

	function find($table,$where,$fields='*',$order=''){
		$row = $this->findAll($table,$where,$fields,$order,1,1);
		return $row[0];
	}

	function findAll($table,$where='',$fields='*',$order='', $page = 0, $pageSize = 10,&$totalCount=0){
		$sql = "select {$fields}  from ".$table." where 1=1 "
		.($where?" and ".$where:"")
		.($order?" order by ".$order:"");
		
		return $this->query($sql,$page, $pageSize,&$totalCount);
	}

	function update($table,$where,$row){
	 	$string = $this->compile_update_string ( $row );
        $sql = 'UPDATE ' . $table . ' SET ' . $string.' WHERE ' . $where;
 
        return $this->execute($sql);
	}

	function create($table,$row,$pkField=''){
		$formated_array = $this->compile_insert_string($row);
        $sql = 'INSERT INTO ' . $table . ' (' . $formated_array ['FIELD_NAMES'] . ') VALUES(' . $formated_array ['FIELD_VALUES'] . ')';
		$stmt = &$this->_execute($sql);
		$nowid = $this->getNewId($table);
		$nowid = $nowid?$nowid:1;
		return $stmt?$nowid:-1;
	}

	function getNewId($table){
		$result = $this->query("select @@IDENTITY as NEWID");
		
		$newid = $result[0]['NEWID'];
		
		if($newid) return $newid; 
	}
	
	function del($table,$where){
		if(is_array($where)){
			$where = $this->compile_update_string ( $where );
		}
        $sql = "DELETE FROM ".$table." WHERE ".$where;
 
        return $this->execute($sql);
	}
	
	/*执行SQL
	参数:
	@params $sql string
	@params $row array 
	*/
	private function &_execute($sql){
		if(empty($this->conn)){
           JYTools::error("Not connect database");
        }
        
        $result = mssql_query ($sql, $this->conn);
        
        if($result === false){
			JYTools::error($sql.": Query String error." . mysqli_error($this->conn));
        }
        
       	return $result;
	}

	public function getTableCol(){
		$sql = "sp_columns ccc";
		$ret = $this->query($sql);
		return $ret;
	}

	/*取下一行数据
	返回:成功，返回结果数组
	*/
	private function fetch(&$stmt){
		$row = @mssql_fetch_array($stmt, MSSQL_ASSOC);
        if ($row && sqlserver::RESULT_FIELD_NAME_LOWER) {
            $row = array_change_key_case($row, CASE_LOWER);
        }
		return $row;
	}

	/*
	*
	*/
	private function fetchArray(&$stmt,$offset=0,$rows=-1){
		//移动游标
		if($offset){
			mssql_data_seek($stmt,$offset);
		}

		$data = array();
		while($row=$this->fetch($stmt)){
			$data[]= $row;
			if($rows>0&&count($data)>=$rows) break;
		}

		if($stmt instanceof mssql_result) mssql_free_result($stmt);
		return $data;
	}
	
 	/**
     * format update set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_update_string($data){
        $return = '';
        foreach( $data as $k => $v ){
            if (is_string($v)||!$v){
                $return .= "$k='" . $this->formatString ( $v ) . "',";
            }else{
                $return .= "$k=" . $v . ",";
            }
        }
        $return = preg_replace ( "/,$/", "", $return );

        return $return;
    }
    
 /**
     * format  insert set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_insert_string($data){
        $field_names = '';
        $field_values = '';

        foreach ( $data as $k => $v ){
            $field_names .= "{$k},";

            if (is_string($v)||!$v){
                $field_values .= "'" . $this->formatString ( $v ) . "',";
            }else{
                $field_values .= $v . ",";
            }
        }

        $field_names = preg_replace ( "/,$/", "", $field_names );
        $field_values = preg_replace ( "/,$/", "", $field_values );

        return array ('FIELD_NAMES' => $field_names, 'FIELD_VALUES' => $field_values );
    }

  /**
     * Escapes special characters in a string for use in a SQL statement
     *
     * @param string $str    the query sql string
     * @return string $str   the formatted string
     */
    public function formatString($str){
        if (get_magic_quotes_gpc ()){
            $str = stripslashes ( $str );
        }

        return $str;
    }

	function close(){
	    if($this->conn != null){
            mssql_close ( $this->conn );
            $this->conn = null;
        }
	}

	function __destruct(){
		$this->close();
	}  
}